# -*- coding: utf-8 -*-
import pymssql
from datetime import datetime

# 数据库连接信息
DB_CONFIG = {
    'server': '(local)',
    'user': 'sa',
    'password': '123456789',
    'database': 'JCDGXT',
    'charset': 'utf8'
}


# 初始化数据库连接
def get_db_connection():
    return pymssql.connect(
        server=DB_CONFIG['server'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database'],
        charset=DB_CONFIG['charset']
    )


# 获取所有订单信息
def all():
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute('SELECT ISBN, aid, bno, quantity, amount, [order time], [pay time] FROM [order$]')
    result = cursor.fetchall()
    conn.close()
    return result


# 插入订单信息
def insert_order(ISBN, aid, bno, quantity, amount, order_time, pay_time):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            'INSERT INTO [order$] (ISBN, aid, bno, quantity, amount, [order time], [pay time]) VALUES (%s, %s, %s, %s, %s, %s, %s)',
            (ISBN, aid, bno, quantity, amount, order_time.strftime('%Y-%m-%d %H:%M:%S'), pay_time.strftime('%Y-%m-%d %H:%M:%S'))
        )
        conn.commit()
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        cursor.close()
        conn.close()

def order_exists(ISBN, aid, bno, order_time, pay_time):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(
        'SELECT 1 FROM [order$] WHERE ISBN = %s AND aid = %s AND bno = %s AND [order time] = %s AND [pay time] = %s',
        (ISBN, aid, bno, order_time, pay_time))
    result = cursor.fetchone()
    conn.close()
    return result is not None


def isbn_exists(ISBN):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT 1 FROM [jiaocai$] WHERE ISBN = %s', (ISBN,))
    result = cursor.fetchone()
    conn.close()
    return result is not None


def aid_exists(aid):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT 1 FROM [administrators$] WHERE aid = %s', (aid,))
    result = cursor.fetchone()
    conn.close()
    return result is not None


def bno_exists(bno):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT 1 FROM [businessmen$] WHERE bno = %s', (bno,))
    result = cursor.fetchone()
    conn.close()
    return result is not None


# 根据ISBN、aid、bno、order time、pay time查询订单信息
def query_by_ISBN_aid_bno_pay_time_order_time(ISBN, aid, bno, pay_time, order_time):
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)  # 使用 as_dict=True 返回字典形式的结果
    cursor.execute(
        'SELECT * FROM [order$] WHERE ISBN = %s AND aid = %s AND bno = %s AND [pay time] = %s AND [order time] = %s',
        (ISBN, aid, bno, pay_time, order_time)
    )
    result = cursor.fetchone()
    conn.close()
    return result



# 根据ISBN、aid、bno、order time、pay time更新订单信息
def update_by_ISBN_aid_bno_order_time_pay_time(ISBN, aid, bno, order_time, pay_time, data):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(
        'UPDATE [order$] SET quantity=%s, amount=%s WHERE ISBN=%s AND aid=%s AND bno=%s AND [order time]=%s AND [pay time]=%s',
        (data['quantity'], data['amount'], ISBN, aid, bno, order_time, pay_time)
    )
    conn.commit()
    conn.close()


# 根据ISBN、aid、bno、order time、pay time删除订单信息
def delete_by_ISBN_aid_bno_order_time_pay_time(ISBN, aid, bno, order_time, pay_time):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM [order$] WHERE ISBN=%s AND aid=%s AND bno=%s AND [order time]=%s AND [pay time]=%s',
                   (ISBN, aid, bno, order_time, pay_time))
    conn.commit()
    conn.close()
